﻿-- =============================================
-- Author:		Dmitry Nazarov
-- Create date: 22.03.2011
-- Description:	<Description,,>

-- =============================================
CREATE PROCEDURE [dbo].[report_received_means_out_invoice] 
	@p_date_from date,
	@p_date_to date,
	@p_nsi_type_of_activity_id	int,
	@p_contract_id	int,
	@p_nsi_organization_id	int,
	@p_invoice_out_id int,
	@p_payment_in_id int,
	@p_sum_from money,
	@p_sum_to money
AS
BEGIN
	SET NOCOUNT ON;

    SELECT o.SHORT_NAME as ORG_NAME, 
    c.NUMBER as CONTRACT_NUMBER, 
    [io].NUMBER as INVOICE_OUT_NUMBER, 
    [io].[DATE] as INVOICE_DATE,
    pst.[TYPE] as PRODUCT_STATE,
    pu.NAME as PRODUCT_NAME, 
    COUNT(*) as COUNT_PROD,
    ISNULL(SUM(pii.SUM_RUB),0) as PROD_PAY_SUM,
    piio.SUM_RUB as PAYMENT_SUM_RUB,
    piio.SUM_CUR as PAYMENT_SUM_CUR,
    [pi].NUMBER as PAYMENT_NUMBER,
    [pi].DATE as PAYMENT_DATE,
    [dbo].f_get_currency_name_by_id ([io].NSI_CURRENCY_ID) as CURRENCY_NAME  
    
    FROM PAYMENT_IN as [pi]
	INNER JOIN PAY_IN_ON_INV_OUT as piio
		ON piio.PAYMENT_IN_ID = [pi].ID
	INNER JOIN INVOICE_OUT as [io]
		ON [io].ID = piio.INVOICE_OUT_ID
	INNER JOIN INVOICE_OUT_ITEM as ioi
		ON [ioi].INVOICE_OUT_ID = io.ID
	INNER JOIN PRODUCT_UNIT as pu
		ON ioi.PRODUCT_UNIT_ID = pu.ID
	LEFT JOIN PAYMENT_IN_ITEM as pii 
		ON pii.PRODUCT_UNIT_ID = pu.ID AND pii.PAYMENT_IN_ID = [pi].ID
	--LEFT JOIN dbo.PU_Payed_By_PayIn(pu.ID, [pi].ID) as pps 
	--	ON pps.PU_ID = pu.ID
	LEFT OUTER JOIN PID 
		ON PID.ID = [pi].PID
	LEFT OUTER JOIN [CONTRACT] as c
		ON c.ID = PID.CONTR_ID
	LEFT OUTER JOIN NSI_ORGANIZATION AS o 
		ON [pi].NSI_ORGANIZATION_ID = o.ID
	INNER JOIN V_NSI_PRODUCT_STATE_TYPE as pst
		ON pst.ID = pu.NSI_PRODUCT_STATE_ID
	WHERE [pi].DATE BETWEEN @p_date_from AND @p_date_to
		AND
		(  
			(@p_nsi_type_of_activity_id is null)
			OR
			(c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id)
		)	
		AND
		(  
			(@p_contract_id is null)
			OR
			(c.ID = @p_contract_id)
		)	
		AND
		(  
			(@p_nsi_organization_id is null)
			OR
			(o.ID = @p_nsi_organization_id)
		)
		AND
		(  
			(@p_invoice_out_id is null)
			OR
			([io].ID = @p_invoice_out_id)
		)
		AND
		(  
			(@p_payment_in_id is null)
			OR
			([pi].ID = @p_payment_in_id)
		)
		AND
		(
			(@p_sum_from is NULL)
			OR
			(piio.SUM_RUB BETWEEN @p_sum_from AND @p_sum_to)
		)
		GROUP BY o.SHORT_NAME, c.NUMBER, io.DATE, io.NUMBER, pi.DATE, pi.NUMBER,
		 pu.NAME, pst.[TYPE],  
		piio.SUM_CUR, piio.SUM_RUB, io.NSI_CURRENCY_ID
END